package com.ecube.mqtt.mysql;

import com.alibaba.fastjson.JSONObject;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;


public class Query {

    static Connection con;
    static Statement sql;
    static ResultSet res;

    public Connection getConnection(String env, String lib) {
        try {
            // 加载数据库驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //Class.forName("com.mysql.cj.jdbc.Driver");
            // 连接数据库
            // 注意此处因为数据库和系统时区差异会引起异常所以在在jdbc连接的url后面加上
            String url = "";
            if (env.equals("UAT")) {
                url = "jdbc:mysql://pc-uf6j29qa2ez19opwm.rwlb.rds.aliyuncs.com:3306/" + lib;
                con = DriverManager.getConnection(url
                        + "?serverTimezone=UTC&characterEncoding=utf-8", "rhdk", "Rhdk@2019");
            } else if (env.equals("FAT")) {
                url = "jdbc:mysql://rm-uf6kl4uuc01g3018i123930.mysql.rds.aliyuncs.com:3306/" + lib;
                con = DriverManager.getConnection(url
                        + "?serverTimezone=UTC&characterEncoding=utf-8", "rhdk", "Rhdk@2019");
            }else if (env.equals("DEV")) {
                url = "jdbc:mysql://10.6.6.73:3306/" + lib;
                con = DriverManager.getConnection(url
                        + "?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8", "root", "rhzl@2014");
                System.out.println(url);
            }else if (env.equals("HG")) {
                if(lib.equals("ecube_swap")||lib.equals("ecube_charge")||lib.equals("ecube_base")){
                    lib="ecube_biz";
                }else if (lib.equals("order")){
                    lib="ecube_order";
                }

                url = "jdbc:mysql://10.9.25.201:3306/" + lib;
                con = DriverManager.getConnection(url
                        + "?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8", "rhdk", "rhdk@2024");
                System.out.println(url);
            }


        } catch (Exception e) {
            System.out.println("连接数据库失败");
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return con;
    }

    //获取充电枪充电snno
    public static String getChargeSnno(String env, String chgID) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_swap");
        String snno = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT snno FROM `charge_cmd` where chg_id='" + chgID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                snno = res.getString("snno");
                System.out.print(" snno:" + snno);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return snno;
    }

    //获取充电枪充电snno
    public static String getChargeOrderSn(String env, String chgID) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_swap");
        String ordersn = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT order_sn FROM `charge_cmd` where chg_id='" + chgID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                ordersn = res.getString("order_sn");
                System.out.print(" order_sn:" + ordersn);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ordersn;
    }
    public static String getStartTime(String env, String externalCode) {

        String stationId = getStationId(env, externalCode);

        Query c1 = new Query();
        con = c1.getConnection(env, "order");
        String startTime = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT business_start_time FROM `order_main` where station_code='" + stationId + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                startTime = res.getString("business_start_time");
                System.out.print(" startTime:" + startTime);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return startTime.substring(0,19);
    }

    public static String getStationId(String env, String externalCode) {

        Query c = new Query();
        con = c.getConnection(env, "ecube_base");

        String stationId = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT t_charge_station_code FROM `t_exchange_station_info` where external_code='" + externalCode + "' and status = 1 and is_deleted = 0 order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                stationId = res.getString("t_charge_station_code");
                System.out.print(" stationId:" + stationId);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return stationId;
    }
    public static String getPileStartTime(String env, String externalCode) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");

        String stationId = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT t_charge_station_code FROM `t_charge_spots_info` where external_code='" + externalCode + "' and is_deleted = 0 order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                stationId = res.getString("t_charge_station_code");
                System.out.print(" stationId:" + stationId);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        Query c1 = new Query();
        con = c1.getConnection(env, "order");
        String startTime = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT business_start_time FROM `order_main` where station_code='" + stationId + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                startTime = res.getString("business_start_time");
                System.out.print(" startTime:" + startTime);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return startTime.substring(0,19);
    }

        //获取车辆rfid
    public static String getrfID(String env, String plate_number) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        String auto_rfid = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT auto_rfid FROM `t_auto_info` where plate_number='" + plate_number + "' and is_deleted=0");        // 执行SQL语句，返回结果集
            while (res.next()) {
                auto_rfid = res.getString("auto_rfid");
                System.out.print(" auto_rfid:" + auto_rfid);
            }
            con.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return auto_rfid;
    }

    //getOrderSn

    public static String getOrderSn(String env, String chgID) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_swap");
        String ordersn = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT order_sn FROM `charge_cmd` where chg_id='" + chgID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                ordersn = res.getString("order_sn");
                System.out.print(" order_sn:" + ordersn);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ordersn;
    }

    //获取车辆rfid
    public static String getvin(String env, String plate_number) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        String vin = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT vin FROM `t_auto_info` where plate_number='" + plate_number + "' and is_deleted=0");        // 执行SQL语句，返回结果集
            while (res.next()) {
                vin = res.getString("vin");
                System.out.print(" vin:" + vin);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return vin;
    }

    //获取换电OrderSN
    public static String getSwapOrderSN(String env, String stationId) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_swap");
        String orderno = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT order_no FROM `swap_cmd` where station_no='" + stationId + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {        // 如果当前语句不是最后一条则进入循环
                orderno = res.getString("order_no");
                System.out.print(" order_sn:" + orderno);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return orderno;
    }

    //获取换电站费率模型id
    public static String getChargeModelId(String env, String externalCode) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        String remodeId = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT id FROM `t_ex_station_billing_model_relation` where external_code='" + externalCode + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                remodeId = res.getString("id");
                System.out.print(" remodeId:" + remodeId);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return remodeId;
    }

    public static String getPileModelId(String env, String rateModelID) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        String modelId = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT billing_model_id FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                modelId = res.getString("billing_model_id");
                System.out.print(" billing_model_id:" + modelId);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return modelId;
    }

    public static Integer getPileFeeType(String env, String rateModelID) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        int feetype = 0;
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT fee_type FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                feetype = res.getInt("fee_type");
                System.out.print(" fee_type:" + feetype);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return feetype;
    }

    public static Integer getPileServiceFeeType(String env, String rateModelID) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        int feetype = 0;
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT service_fee_type FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                feetype = res.getInt("service_fee_type");
                System.out.print(" service_fee_type:" + feetype);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return feetype;
    }

    public static Float getPilePrice(String env, String rateModelID, String rateType) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        Float price = 0F;
        try {
            sql = con.createStatement();        // 实例化Statement对象
            if(rateType.equals("1")){
                res = sql.executeQuery("SELECT tip_real_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("2")){
                res = sql.executeQuery("SELECT peak_real_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("3")){
                res = sql.executeQuery("SELECT level_real_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("4")){
                res = sql.executeQuery("SELECT valley_real_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("5")){
                res = sql.executeQuery("SELECT deep_valley_real_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("unit_price")){
                res = sql.executeQuery("SELECT unit_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }

            while (res.next()) {
                price = res.getFloat("price");
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return price;
    }

    public static Float getPileServicePrice(String env, String rateModelID, String rateType) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        Float price = 0F;
        try {
            sql = con.createStatement();        // 实例化Statement对象
            if(rateType.equals("1")){
                res = sql.executeQuery("SELECT tip_service_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("2")){
                res = sql.executeQuery("SELECT peak_service_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("3")){
                res = sql.executeQuery("SELECT level_service_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("4")){
                res = sql.executeQuery("SELECT valley_service_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("5")){
                res = sql.executeQuery("SELECT deep_valley_service_price AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }
            if(rateType.equals("unit_service_charge")){
                res = sql.executeQuery("SELECT unit_service_charge AS price FROM `t_station_billing_model_relation` where id='" + rateModelID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            }

            while (res.next()) {
                price = res.getFloat("price");
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return price;
    }

    public static String getModelId(String env, String externalCode) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        String modelId = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT billing_model_id FROM `t_ex_station_billing_model_relation` where external_code='" + externalCode + "' order by id desc limit 1");        // 执行SQL语句，返回结果集

            while (res.next()) {
                modelId = res.getString("billing_model_id");
                System.out.print(" billing_model_id:" + modelId);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return modelId;
    }

    public static String getVersion(String env, String externalCode) {
        Query c = new Query();
        con = c.getConnection(env, "iot_device");
        String version = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT protocol_version FROM `device` where code='" + externalCode + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                version = res.getString("protocol_version");
                System.out.print(" protocol_version:" + version);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return version;
    }
    public static String getSVersion(String env, String externalCode) {
        Query c = new Query();
        con = c.getConnection(env, "iot_device");
        String version = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT version FROM `device` where code='" + externalCode + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                version = res.getString("version");
                System.out.print(" version:" + version);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return version;
    }

    public static String getSwapSn(String env, String externalCode) {
        String stationId = getStationId(env, externalCode);

        Query c = new Query();
        con = c.getConnection(env, "order");
        String swapSn = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT business_code FROM `order_main` where station_code='" + stationId + "' and order_type=2 order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                swapSn = res.getString("business_code");
                System.out.print(" swapSn:" + swapSn);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return swapSn;
    }

    public static String getProductCode(String env, String externalCode) {
        Query c = new Query();
        con = c.getConnection(env, "iot_device");
        String code = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT product_code FROM `device` where code='" + externalCode + "'and is_deleted=0 order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                code = res.getString("product_code");
                System.out.print(" product_code:" + code);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(code.length()==0){
            code = "100030001";
        }
        return code + "/";
    }

    public static String getPileCode(String env, String externalCode) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        String code = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT associated_sources FROM `t_charge_spots_info` where external_code='" + externalCode + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                code = res.getString("associated_sources");
                System.out.print(" associated_sources:" + code);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(code.length()==0){
            code = "100040002";
        }
        return code + "/";
    }

    public static List<Map<String, String>> getbillingTimeList(String env, String modelId, String startTime, String endTime) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_base");
        List<Map<String, String>> times = new ArrayList();
        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String dates = dateFormat.format(date);

        String lastDay = "";
        Calendar cal = Calendar.getInstance();
        cal.setTime(new Date());
        cal.add(Calendar.DATE, 1);
        Date date2 = cal.getTime();
        lastDay = dateFormat.format(date2);



        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT time_type, start_time, end_time FROM `t_billing_model_detail` where model_id='" + modelId + "'");        // 执行SQL语句，返回结果集

            while (res.next()) {

                Map time = new HashMap();
                String time_type = res.getString("time_type");
                String start_time = res.getString("start_time");
                String end_time = res.getString("end_time");
                time.put("time_type", time_type);
                time.put("start_time", dates + " " + start_time + ":00");
                if(!end_time.equals("00:00")){
                    time.put("end_time", dates + " " + end_time + ":00");
                }else {
                    time.put("end_time", lastDay + " " + "00:00:00");
                }

                times.add(time);

            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        for (int x=0; x<times.size(); x++){
            for (int y = 0; y < times.size()-1; y++) {
                if(getTime(times.get(y+1).get("start_time")) < getTime(times.get(y).get("start_time"))){
                    Map<String, String> map = times.get(y);
                    times.set(y,times.get(y+1));
                    times.set(y+1,map);
                }
            }

        }

        int start = 0;
        int end = 0;
        List<Map<String, String>> listTimes = new ArrayList<>();
        Map<String, String> endMap = new HashMap<String, String>();

        for (int x = 0; x < times.size(); x++) {

            if(getTime(times.get(x).get("start_time")) < getTime(startTime) && getTime(startTime) < getTime(times.get(x).get("end_time"))){
                if(getTime(times.get(x).get("start_time")) < getTime(endTime) && getTime(endTime) < getTime(times.get(x).get("end_time"))){
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("ratetype", timeTypeToRateType(times.get(x).get("time_type")));
                    map.put("start_time", startTime);
                    map.put("end_time", endTime);
                    listTimes.add(map);
                    return listTimes;
                }else{
                    start = x;
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("ratetype", timeTypeToRateType(times.get(x).get("time_type")));
                    map.put("start_time", startTime);
                    map.put("end_time", times.get(x).get("end_time"));
                    listTimes.add(map);
                }
            }


        }

        System.out.println(startTime);
        System.out.println(endTime);
        System.out.println(modelId);
        if(getTime(endTime) < getTime(times.get(times.size()-1).get("end_time"))){
            for (int x = 0; x < times.size(); x++) {
                if (getTime(times.get(x).get("start_time")) < getTime(endTime) && getTime(endTime) < getTime(times.get(x).get("end_time"))) {
                    end = x;
                    endMap.put("ratetype", timeTypeToRateType(times.get(x).get("time_type")));
                    endMap.put("start_time", times.get(x).get("start_time"));
                    endMap.put("end_time", endTime);
                }
            }
        }else {
            end = -1;
            endMap.put("ratetype", timeTypeToRateType(times.get(0).get("time_type")));
            endMap.put("start_time", times.get(times.size()-1).get("end_time"));
            endMap.put("end_time", endTime);

        }
        if(end == -1){
            for (int i = start+1; i < times.size(); i++) {
                times.get(i).put("ratetype", timeTypeToRateType(times.get(i).get("time_type")));
                times.get(i).remove("time_type");
                listTimes.add(times.get(i));
            }
        }

        if(end - start > 1){
            for (int i = start+1; i < end; i++) {
                times.get(i).put("ratetype", timeTypeToRateType(times.get(i).get("time_type")));
                times.get(i).remove("time_type");
                listTimes.add(times.get(i));
            }

        }
        listTimes.add(endMap);
        return listTimes;
    }

    public static String timeTypeToRateType(String timeType){
        if(timeType.equals("0")){
            return "1";
        }
        if(timeType.equals("1")){
            return "2";
        }
        if(timeType.equals("2")){
            return "3";
        }
        if(timeType.equals("3")){
            return "4";
        }
        if(timeType.equals("4")){
            return "5";
        }
        return "";
    }

    public static Long getTime(String time){
        Date date = new Date();
        try {
            date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(time);
        }catch (Exception e) {
            e.printStackTrace();
        }
        return date.getTime();
    }


    //获取换电站费率模型id
    public static String getSwapChargeModelId(String env, String gunId) {

        String chargesportid = "";
        try {
            Query s = new Query();
            con = s.getConnection(env, "ecube_base");
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT charge_spot_id FROM `t_charge_gun_info` where external_code='" + gunId + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                chargesportid = res.getString("charge_spot_id");
                System.out.print(" chargesportid:" + chargesportid);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        String chargesportcode = "";
        try {
            Query s = new Query();
            con = s.getConnection(env, "ecube_base");
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT t_charge_station_code FROM `t_charge_spots_info` where id='" + chargesportid + "'");        // 执行SQL语句，返回结果集
            while (res.next()) {
                chargesportcode = res.getString("t_charge_station_code");
                System.out.print(" chargesportcode:" + chargesportcode);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        String remodeId = "";
        try {
            Query c = new Query();
            con = c.getConnection(env, "ecube_base");
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT station_free_id FROM `t_charge_spots_billing_model` where charge_station_code='" + chargesportcode + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {
                remodeId = res.getString("station_free_id");
                System.out.print(" remodeId:" + remodeId);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }


        return remodeId;
    }

    //获取充电桩snno
    public static String getSnno(String env, String chgID) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_charge");
        String snno = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT snno FROM `charging_pile_cmd` where chg_code='" + chgID + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {        // 如果当前语句不是最后一条则进入循环
                snno = res.getString("snno");
                System.out.print(" snno:" + snno);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return snno;
    }


    //获取充电桩OrderSN
    public static String getOrderSN(String env, String chgID, String time) {
        Query c = new Query();
        con = c.getConnection(env, "ecube_charge");
        String ordersn = "";
        try {
            sql = con.createStatement();        // 实例化Statement对象
            res = sql.executeQuery("SELECT order_sn FROM `charging_pile_cmd` where chg_code='" + chgID + "' and create_time >'" + time + "' order by id desc limit 1");        // 执行SQL语句，返回结果集
            while (res.next()) {        // 如果当前语句不是最后一条则进入循环
                ordersn = res.getString("order_sn");
                System.out.print(" order_sn:" + ordersn);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ordersn;
    }

//    public static void main(String[] args) {
//        System.out.println(getbillingTimeList("UAT","298", "2023-03-29 03:00:00", "2023-03-30 01:00:00"));
//        System.out.println(JSONObject.toJSONString(getbillingTimeList("UAT","298", "2023-03-29 03:00:00", "2023-03-30 01:00:00")));
//    }

}
